# 爬取模拟外汇网站数据
import urllib.request
import re
import sqlite3


class MySpider:
    def openDB(self):
        # 建立数据库连接（没有该数据库会自动创建）
        self.conn = sqlite3.connect('my_sql.db')
        self.cursor = self.conn.cursor()
        # 如果有rates表就删除
        try:
            self.cursor.execute("drop table rates")
        except:
            pass
        # 执行sql语句创建数据表
        sql = "CREATE TABLE IF NOT EXISTS rates (Currency varchar(256) PRIMARY KEY,TSP float,CSP float, TBP float,CBP float)"
        self.cursor.execute(sql)

    def closeDB(self):
        # 关闭连接
        self.conn.commit()
        self.conn.close()

    def insertDB(self, currency, tsp, csp, tbp, cbp):
        # 通过sql语句向数据表插入数据
        try:
            # 传入语句时传入参数
            sql = "INSERT INTO rates (Currency, TSP, CSP, TBP, CBP) VALUES (?,?,?,?,?)"
            self.cursor.execute(sql, [currency, tsp, csp, tbp, cbp])
        except Exception as e:
            print(e)

    def show(self):
        # 将数据通过格式控制输出到终端（从表中输出）
        self.cursor.execute("SELECT Currency, TSP, CSP, TBP, CBP FROM rates")
        rows = self.cursor.fetchall()
        print("%-18s%-12s%-12s%-12s%-12s" % ("Currency", "TSP", "CSP", "TBP", "CBP"))
        for row in rows:
            print("%-18s%-12.2f%-12.2f%-12.2f%-12.2f" % (row[0], row[1], row[2], row[3], row[4]))

    def spider(self, url):
        try:
            resp = urllib.request.urlopen(url)
            data = resp.read()
            html = data.decode()
            # 正则表达式匹配
            # 在正则表达式中，r""（双引号前面加上r）表示一个原始字符串，这样可以避免反斜杠字符在正则表达式中被解释
            p = re.search(r"<tr>", html)
            q = re.search(r"</tr>", html)
            i = 0
            while p and q:
                a = p.end()
                b = q.start()
                tds = html[a:b]
                m = re.search(r"<td>", tds)
                n = re.search(r"</td>", tds)
                row = []

                while m and n:
                    u = m.end()
                    v = n.start()
                    row.append(tds[u:v].strip("\n"))
                    tds = tds[n.end():]
                    m = re.search(r"<td>", tds)
                    n = re.search(r"</td>", tds)
                i += 1
                if i >= 2 and len(row) == 6:
                    currency = row[0]
                    tsp = float(row[2])
                    csp = float(row[3])
                    tbp = float(row[4])
                    cbp = float(row[5])
                    self.insertDB(currency, tsp, csp, tbp, cbp)
                html = html[q.end():]
                p = re.search(r"<tr>", html)
                q = re.search(r"</tr>", html)
        except Exception as e:
            print(e)

    def process(self):
        self.openDB()
        self.spider("http://127.0.0.1:5000")
        self.show()
        self.closeDB()


# 主程序
spider = MySpider()
spider.process()

